﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace FirmaTransportowa
{
    public class Vehicle : IModel<Vehicle>
    {
        [System.ComponentModel.DisplayName("ID pojazdu")]
        public int id { get; set; }
        [System.ComponentModel.DisplayName("ID prawa jazdy")]
        public int licence_id { get; set; }
        [System.ComponentModel.DisplayName("Rodzaj pojazdu")]
        public String car_type { get; set; }
        [System.ComponentModel.DisplayName("Pojemność")]
        public int capacity { get; set; }
        [System.ComponentModel.DisplayName("Numer silnika")]
        public String engine_number { get; set; }
        [System.ComponentModel.DisplayName("Pojemność skokowa")]
        public int swept_volume { get; set; }
        [System.ComponentModel.DisplayName("Marka")]
        public String brand { get; set; }
        [System.ComponentModel.DisplayName("Model")]
        public String model { get; set; }

        public Boolean active { get; set; }

        public Vehicle(int vehicle_id, int licence_id, string car_type, int capacity, string engine_number, int swept_volume, string brand, string model)
        {
            this.id = vehicle_id;
            this.licence_id = licence_id;
            this.car_type = car_type;
            this.capacity = capacity;
            this.engine_number = engine_number;
            this.swept_volume = swept_volume;
            this.brand = brand;
            this.model = model;
            this.active = true;
        }

        public Vehicle()
        {

        }

        public int ReturnValue
        {
            get
            {
                return this.id;
            }
        }

        public int save()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            int createdId = -1;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                bool newObject = false;

                if (Vehicle.getById(this.id) == null)
                {
                    cmd.CommandText = "INSERT INTO Vehicle(licence_ID,car_type,capacity,engine_number,swept_volume,brand,model,active) VALUES(@licence_ID,@car_type,@capacity,@engine_number,@swept_volume,@brand,@model,@active) SELECT CONVERT(int, SCOPE_IDENTITY())";
                    newObject = true;
                }
                else
                {
                    cmd.CommandText = "UPDATE Vehicle SET licence_ID=@licence_ID,car_type=@car_type,capacity=@capacity,engine_number=@engine_number,swept_volume=@swept_volume,brand=@brand,model=@model,active=@active WHERE vehicle_ID=@id";
                    cmd.Parameters.AddWithValue("@id", this.id);
                }

                cmd.Parameters.AddWithValue("@licence_ID", this.licence_id);
                cmd.Parameters.AddWithValue("@car_type", this.car_type);
                cmd.Parameters.AddWithValue("@capacity", this.capacity);
                cmd.Parameters.AddWithValue("@engine_number", this.engine_number);
                cmd.Parameters.AddWithValue("@swept_volume", this.swept_volume);
                cmd.Parameters.AddWithValue("@brand", this.brand);
                cmd.Parameters.AddWithValue("@model", this.model);
                cmd.Parameters.AddWithValue("@active", this.active);
                cmd.Connection = conn;

                if (newObject)
                {
                    createdId = (int)cmd.ExecuteScalar();
                }
                else
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                System.Console.Write(e.Message + "\n" + e.StackTrace);
            }

            return createdId;
        }

        public static List<Vehicle> list()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            List<Vehicle> list = new List<Vehicle>();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select * from Vehicle", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Vehicle vehicle = Vehicle.getById(int.Parse(reader["vehicle_ID"].ToString()));

                        list.Add(vehicle);
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }


        public static Vehicle getById(int id)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                if (id < 0)
                {
                    throw new ArgumentException();
                }

                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Vehicle WHERE vehicle_ID=@id;";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Connection = conn;
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    Vehicle vehicle = new Vehicle();
                    vehicle.id = int.Parse(reader["vehicle_ID"].ToString());
                    vehicle.licence_id = int.Parse(reader["licence_ID"].ToString());
                    vehicle.car_type = reader["car_type"].ToString();
                    vehicle.capacity = int.Parse(reader["capacity"].ToString());
                    vehicle.engine_number = reader["engine_number"].ToString();
                    vehicle.swept_volume = int.Parse(reader["swept_volume"].ToString());
                    vehicle.brand = reader["brand"].ToString();
                    vehicle.model = reader["model"].ToString();
                    vehicle.active = Boolean.Parse(reader["active"].ToString());

                    conn.Close();
                    return vehicle;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();

            }

            return null;

        }

        public static Vehicle getByEngineNumber(string engineNumber)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Vehicle WHERE engine_number=@engineNumber;";
                cmd.Parameters.AddWithValue("@engineNumber", engineNumber);
                cmd.Connection = conn;
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    Vehicle vehicle = new Vehicle();
                    vehicle.id = int.Parse(reader["vehicle_ID"].ToString());
                    vehicle.licence_id = int.Parse(reader["licence_ID"].ToString());
                    vehicle.car_type = reader["car_type"].ToString();
                    vehicle.capacity = int.Parse(reader["capacity"].ToString());
                    vehicle.engine_number = reader["engine_number"].ToString();
                    vehicle.swept_volume = int.Parse(reader["swept_volume"].ToString());
                    vehicle.brand = reader["brand"].ToString();
                    vehicle.model = reader["model"].ToString();
                    vehicle.active = Boolean.Parse(reader["active"].ToString());

                    conn.Close();
                    return vehicle;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();

            }

            return null;

        }

        public static BindingSource getVehicles()
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT vehicle_ID, licence_ID, car_type, capacity, engine_number, swept_volume, brand, model FROM dbo.Vehicle WHERE active = 1 ", connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }

        public static bool checkIfRecordExists(string checkedText, string desiredTable)
        {
            bool result = false;

            SqlConnection dbConnection2 = SqlConnectionFactory.getConnection();

            dbConnection2.Open();

            try
            {
                string sql = "SELECT * FROM " + desiredTable + " WHERE engine_number='" + checkedText + "' AND active=1";

                SqlCommand cmd = new SqlCommand(sql, dbConnection2);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    result = true;
                    MessageBox.Show("Podany numer silnika juz istnieje w bazie!");
                }
                reader.Close();
            }
            finally
            {
                dbConnection2.Close();
            }

            return result;
        }

        public static int getIdByEngineNumber(long engineNumber)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT vehicle_ID FROM Vehicle WHERE engine_number = '" + engineNumber + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        return int.Parse(reader["vehicle_ID"].ToString());
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return -1;
        }

        public void delete()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Vehicle WHERE vehicle_ID=@id";
                cmd.Parameters.AddWithValue("@id", this.id);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }
        }

        public static Predicate<Vehicle> ById(int id)
        {
            return delegate(Vehicle vehicle)
            {
                return vehicle.id == id;
            };
        }
    }
}
